﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data.Common;
using WebCurriculumn.Helps;

namespace WebCurriculumn.DBAcess
{
    /// <summary>
    /// 数据库执行结果类
    /// </summary>
    public class SqlResult
    {
        /// <summary>
        /// 结果集
        /// </summary>
        public DataSet DataSet { get; set; }
        /// <summary>
        /// out 参数结果集合
        /// </summary>
        public IDictionary<string, object> OutResult { get; set; }

        public SqlResult(string procedureName)
        {
            this.DataSet = new DataSet(procedureName);
            this.OutResult = new Dictionary<string, object>();
        }
    }
    /// <summary>
    /// 数据库操作上下文类
    /// </summary>
    public class DBContext : IDisposable
    {
        /// <summary>
        /// 数据库操作异常类
        /// </summary>
        class DBContextException : DbException
        {
            public DBContextException(string message)
                : base(message)
            {
            }
        }

        private SqlConnection dbConn_;
        /// <summary>
        /// 
        /// </summary>
        private SqlTransaction trans = null;


        private bool inTrans = false;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DBContext()
        {
            // 根据需要定义自己的连接字符串
            dbConn_ = new SqlConnection(ConfigurationManager.ConnectionStrings["WebCurriculumnDB"].ConnectionString);
            try
            {
                dbConn_.Open();
            }
            catch (Exception ex)
            {
                MyUtils.WriteEvent("WEB RESERVATION", 1100, "0", ex.Message);
            }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public DBContext(string connectionString)
        {
            dbConn_ = new SqlConnection(connectionString);
            try
            {
                dbConn_.Open();
            }
            catch (Exception ex)
            {
                MyUtils.WriteEvent("WEB RESERVATION", 1100, "0", ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public void BeginTrans()
        {
            //トランザクションの開始
            this.trans = dbConn_.BeginTransaction();
            //トランザクション中フラグを設定する
            this.inTrans = true;
        }
        /// <summary>
        /// 
        /// </summary>
        public void RollBack()
        {
            //
            try
            {
                if (this.inTrans)
                {
                    this.trans.Rollback();
                }
            }
            catch (Exception ex)
            {
                //何もしない
                throw ex;
            }
            finally
            {
                //トランザクションフラグをリセットする
                this.inTrans = false;
            }

        }


        /// <summary>
        /// 
        /// </summary>
        public void Commit()
        {
            try
            {
                if (this.inTrans)
                {
                    this.trans.Commit();
                }
            }
            catch (Exception ex)
            {
                throw ex;
                //何もしない
            }
            finally
            {
                //トランザクションフラグをリセットする
                this.inTrans = false;
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlResult CallProcedure(string procedureName, params object[] parameters)
        {
            if (this.dbConn_ == null || this.dbConn_.State == ConnectionState.Closed)
            {
                throw new DBContextException("データベース接続が切断されている。");
            }
            if (parameters == null) parameters = new object[] { null };

            // SqlResult是自己定义的用于保存结果数据集、返回值、传出参数集的类
            SqlResult result = new SqlResult(procedureName);

            SqlCommand dbCmd = new SqlCommand(procedureName, dbConn_);
            if (this.inTrans)
                dbCmd.Transaction = this.trans;
            dbCmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter myAdapter = new SqlDataAdapter(dbCmd);

            SqlParameter[] @params = GetProcedureParameter(procedureName, parameters);
            // 获得和创建存储过程的参数,并且设置好值
            if (@params.Length > 0)
            {
                dbCmd.Parameters.AddRange(@params);
            }

            myAdapter.Fill(result.DataSet, "Table");

            // 获得存储过程的传出参数值和名字对,保存在一个Hashtable中
            SetOutputValue(result, dbCmd.Parameters);

            // 在这里释放各种资源,断开连接
            myAdapter.Dispose();
            dbCmd.Dispose();

            return result;
        }

        public SqlResult CallSqlStatement(string sqlStatement)
        {
            if (this.dbConn_ == null || this.dbConn_.State == ConnectionState.Closed)
            {
                throw new DBContextException("データベース接続が切断されている。");
            }
            SqlResult result = new SqlResult("name");

            SqlCommand myCommand2 = new SqlCommand();
            myCommand2.Connection = this.dbConn_;
            if (this.inTrans)
                myCommand2.Transaction = this.trans;
            myCommand2.CommandText = sqlStatement;

            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand2);
            myAdapter.Fill(result.DataSet, "Table");
            // 在这里释放各种资源,断开连接
            myAdapter.Dispose();
            myCommand2.Dispose();
            return result;
        }

        /// <summary>
        /// 存储过程的参数取得
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlParameter[] GetProcedureParameter(string procedureName, params object[] parameters)
        {
            List<SqlParameter> result = new List<SqlParameter>();

            SqlCommand myCommand2 = new SqlCommand();
            myCommand2.Connection = this.dbConn_;
            if (this.inTrans)
                myCommand2.Transaction = this.trans;
            myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + procedureName + "' order by ORDINAL_POSITION";

            SqlDataReader reader = null;
            reader = myCommand2.ExecuteReader();

            // 创建返回参数
            SqlParameter parameter = null;
            int i = 0;
            // 创建各个参数,在这个地方可以自动的创建SqlParameter的类型,值,方向等属性
            while (reader.Read())
            {
                parameter = new SqlParameter();
                string parameterName = reader["PARAMETER_NAME"].ToString();
                if (parameters.Length > 0)
                    parameter = new SqlParameter(parameterName, i >= parameters.Length ? null : parameters[i]);
                else
                    parameter = new SqlParameter(parameterName, null);
                parameter.Direction = reader["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output;
                parameter.DbType = GetParameterType(reader["DATA_TYPE"].ToString());
                result.Add(parameter);
                i++;
            }
            reader.Close();

            return result.ToArray();
        }
        /// <summary>
        /// 获取out参数的值
        /// </summary>
        /// <param name="result"></param>
        /// <param name="sqlParameterCollection"></param>
        private void SetOutputValue(SqlResult result, SqlParameterCollection sqlParameterCollection)
        {
            foreach (SqlParameter param in sqlParameterCollection)
            {
                if (param.Direction == ParameterDirection.Output ||
                    param.Direction == ParameterDirection.InputOutput)
                {
                    result.OutResult.Add(param.ParameterName, param.Value);
                }
            }
        }

        private DbType GetParameterType(string type)
        {
            switch (type)
            {
                case "int":
                case "smallint":
                    return DbType.Int32;
                case "varchar":
                    return DbType.String;
                case "datetime":
                    return DbType.DateTime;
                case "float":
                    return DbType.Double;
                default:
                    return DbType.String;
            }

        }

        /// <summary>
        /// 数据库操作上下文关闭
        /// </summary>
        public void Close()
        {
            if (this.dbConn_ != null && this.dbConn_.State != ConnectionState.Closed)
            {
                this.dbConn_.Close();
                this.dbConn_.Dispose();
                this.dbConn_ = null;
            }
        }

        #region IDisposable 成员

        /// <summary>
        /// 
        /// </summary>
        void IDisposable.Dispose()
        {
            this.Close();
        }

        #endregion
    }
}
